//    Copyright (C) 2013  <lugocreation@gmail.com>
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>.
package org.jpgolay.me.persistence;

import java.sql.*;
import java.util.*;
import org.jpgolay.me.db.Db;
import org.jpgolay.me.util.MEDate;

// Referenced classes of package org.jpgolay.me.persistence:
//            ConnectDB, SQLListService
public class ObjectServiceCRUD {

    public ObjectServiceCRUD() {
        init();
    }

    public Object create(int type, String arg0) {
        return createSQL(type, arg0);
    }

    public Object read(Object pk, int type) {
        return readSQL(pk, type);
    }

    public Collection readAll(Object pk, int type) {
        return readSQLAll(pk, null, type);
    }

    public Collection readAll(Object pk, Object arg1, int type) {
        return readSQLAll(pk, arg1, type);
    }

    public boolean update(Object obj, int type) {
        return updateSQL(obj, type);
    }

    public boolean update(Object obj0, Object obj1, int type) {
        return updateSQL(obj0, obj1, type);
    }

    public boolean delete(Object pk, String lastUpdate, int type) {
        return deleteSQL(pk, lastUpdate, type);
    }

    private void init() {
        con = ConnectDB.getConnectionService();
        date = new MEDate();
    }

    private Object createSQL(int type, String arg0) {
        IfExist ie = null;
        try {
            switch (type) {
                case 51: // '3'
                    pst = con.prepareStatement("INSERT INTO db (id,update_time) VALUES (?,?)");
                    Integer idDb = getNewId("SELECT MAX(id)+1 FROM db");
                    pst.setInt(1, idDb.intValue());
                    pst.setString(2, (new MEDate()).getDateTimeDB());
                    pst.execute();
                    return idDb;
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private Object readSQL(Object pk, int type) {
        try {
            switch (type) {
                case 51: // '3'
                    pst = con.prepareStatement("SELECT id,name,dbname,login,password,url,admin_login,admin_password,admin_url,notes,update_user_id,update_time,date FROM db WHERE id=?");
                    pst.setInt(1, ((Integer) pk).intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        Db db = new Db();
                        db.setId(new Integer(rs.getInt(1)));
                        db.setName(rs.getString(2));
                        db.setDbname(rs.getString(3));
                        db.setLogin(rs.getString(4));
                        db.setPassword(rs.getString(5));
                        db.setUrl(rs.getString(6));
                        db.setAdminLogin(rs.getString(7));
                        db.setAdminPassword(rs.getString(8));
                        db.setAdminUrl(rs.getString(9));
                        db.setNotes(rs.getString(10));
                        db.setUpdateLastUserId(rs.getString(11));
                        db.setUpdateLastTime(rs.getString(12));
                        db.setDate(date.toME(rs.getString(13)));
                        return db;
                    } else {
                        return null;
                    }
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private Collection readSQLAll(Object pk, Object arg1, int type) {
        ArrayList list = new ArrayList();
        try {
            switch (type) {
                case 51: // '3'
                    pst = con.prepareStatement("SELECT id,name,dbname,login,password,url,admin_login,admin_password,admin_url,notes,update_user_id,update_time,date FROM db");
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        do {
                            Db db = new Db();
                            db.setId(new Integer(rs.getInt(1)));
                            db.setName(rs.getString(2));
                            db.setDbname(rs.getString(3));
                            db.setLogin(rs.getString(4));
                            db.setPassword(rs.getString(5));
                            db.setUrl(rs.getString(6));
                            db.setAdminLogin(rs.getString(7));
                            db.setAdminPassword(rs.getString(8));
                            db.setAdminUrl(rs.getString(9));
                            db.setNotes(rs.getString(10));
                            db.setUpdateLastUserId(rs.getString(11));
                            db.setUpdateLastTime(rs.getString(12));
                            db.setDate(date.toME(rs.getString(13)));
                            list.add(db);
                        } while (rs.next());
                        return list;
                    } else {
                        return null;
                    }
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private boolean updateSQL(Object obj, int type) {
        try {
            switch (type) {
                case 51: // '3'
                    Db db = (Db) obj;
                    pst = con.prepareStatement("UPDATE db SET id=?,name=?,dbname=?,login=?,password=?,url=?,admin_login=?,admin_password=?,admin_url=?,notes=?,update_user_id=?,update_time=?,date=? WHERE id=? AND update_time=?");
                    pst.setInt(1, db.getId().intValue());
                    pst.setString(2, db.getName());
                    pst.setString(3, db.getDbname());
                    pst.setString(4, db.getLogin());
                    pst.setString(5, db.getPassword());
                    pst.setString(6, db.getUrl());
                    pst.setString(7, db.getAdminLogin());
                    pst.setString(8, db.getAdminPassword());
                    pst.setString(9, db.getAdminUrl());
                    pst.setString(10, db.getNotes());
                    pst.setString(11, db.getUpdateLastUserId());
                    pst.setString(12, (new MEDate()).getDateTimeDB());
                    pst.setString(13, date.toMysql(db.getDate()));
                    pst.setInt(14, db.getId().intValue());
                    pst.setString(15, db.getUpdateLastTime());
                    pst.execute();
                    return pst.getUpdateCount() != 0;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private boolean updateSQL(Object obj0, Object obj1, int type) {
        return false;
    }

    private boolean deleteSQL(Object pk, String lastUpdate, int type) {
        try {
            switch (type) {
                case 51: // '3'
                    pst = con.prepareStatement("DELETE FROM db WHERE id=? AND update_time=?");
                    pst.setInt(1, ((Integer) pk).intValue());
                    pst.setString(2, lastUpdate);
                    pst.execute();
                    return true;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private Integer getNewId(String request) {
        try {
            SQLListService list = new SQLListService();
            if (list.search(request)) {
                Vector rows = list.getRows();
                if ((String) rows.firstElement() == null) {
                    return new Integer(1);
                } else {
                    return new Integer((String) rows.firstElement());
                }
            } else {
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    private PreparedStatement pst;
    private ResultSet rs;
    private Connection con;
    private MEDate date;
}
